package com.wxy.student.dao;


import com.wxy.student.entity.Student;
import com.wxy.student.entity.StudentInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

public interface StudentDao extends JpaRepository<Student, Integer> {




    void deleteById(Integer id);

    @Query(name = "findByGradeId", nativeQuery = true, value =
            "SELECT s.*" +
                    " FROM `student` s  inner join student_grade sg on s.id=sg.user_id" +
                    " inner join grade g on sg.grade_id = g.id where g.id = ?1")
    List<Student> findByGradeId(Integer id);


    @Query(name = "list", nativeQuery = true, value =
            "SELECT s.*" +
                    " FROM `student` s  inner join student_grade sg on s.id=sg.user_id" +
                    " inner join grade g on sg.grade_id = g.id ")
    List<Student> list();

    @Transactional
    @Modifying
    @Query(name = "deleteByGradeId", nativeQuery = true, value =
            "delete  from `student` where grade_id= ?1")
    int deleteByGradeId(Integer id);

    @Transactional
    @Modifying
    @Query(name = "deleteByStudentGrade", nativeQuery = true, value =
            "delete  from student_grade where grade_id= ?1")
    int deleteByStudentGrade(Integer id);


}